# -------------------------
# Settings
# -------------------------
from flask import request, jsonify
from main import app  
from database.db import fetch_all, fetch_one, execute, transaction
from dateutil.parser import parse as parse_date
from datetime import date

# From main.py import functions
from main import _normalize_gender, validate_patient_payload, paginate_query

# -------------------------
# REST API (JSON)
# -------------------------

# Patients endpoints (pagination & search)
@app.route('/api/patients', methods=['GET'])
def api_patients_list():
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 20))
    name = request.args.get('name')
    if name:
        base = "SELECT * FROM patient WHERE name ILIKE %s ORDER BY patient_id"
        params = [f"%{name}%"]
        rows = paginate_query(base, params, page, per_page)
        return jsonify(rows), 200
    base = "SELECT * FROM patient ORDER BY patient_id"
    rows = paginate_query(base, [], page, per_page)
    return jsonify(rows), 200

@app.route('/api/patients/<int:pid>', methods=['GET'])
def api_patient_get(pid):
    row = fetch_one("SELECT * FROM patient WHERE patient_id=%s", (pid,))
    if not row:
        return jsonify({'error': 'not found'}), 404
    return jsonify(row), 200

@app.route('/api/patients', methods=['POST'])
def api_patient_create():
    data = request.get_json()
    try:
        payload = validate_patient_payload(data)
        execute("""
            INSERT INTO patient (name, gender, age, phone, email, address, medical_history)
            VALUES (%s,%s,%s,%s,%s,%s,%s)
        """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
              payload['email'], payload['address'], payload['medical_history']))
        return jsonify({'message': 'created'}), 201
    except ValueError as ve:
        return jsonify({'error': str(ve)}), 400
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/patients/<int:pid>', methods=['PUT'])
def api_patient_update(pid):
    data = request.get_json()
    try:
        payload = validate_patient_payload(data)
        execute("""
            UPDATE patient SET name=%s, gender=%s, age=%s, phone=%s, email=%s, address=%s, medical_history=%s
            WHERE patient_id=%s
        """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
              payload['email'], payload['address'], payload['medical_history'], pid))
        return jsonify({'message': 'updated'}), 200
    except ValueError as ve:
        return jsonify({'error': str(ve)}), 400
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/patients/<int:pid>', methods=['DELETE'])
def api_patient_delete(pid):
    try:
        execute("DELETE FROM patient WHERE patient_id=%s", (pid,))
        return jsonify({'message': 'deleted'}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# Appointments available (list staff free on date)
@app.route('/api/appointments/available', methods=['GET'])
def api_appointments_available():
    date_str = request.args.get('Date')
    if not date_str:
        return jsonify({'error': 'missing date param'}), 400
    try:
        dt = parse_date(date_str).date()
    except Exception:
        return jsonify({'error': 'invalid date format'}), 400
    rows = fetch_all("""
        SELECT staff_id, name FROM staff
        WHERE staff_id NOT IN (
            SELECT staff_id FROM appointment WHERE appointment_date=%s
        )
    """, (dt,))
    return jsonify(rows), 200

# Appointments details (complex join)
@app.route('/api/appointments/details', methods=['GET'])
def api_appointments_details():
    rows = fetch_all("""
        SELECT a.appointment_id, a.appointment_date, a.status,
               p.name AS patient_name, s.name AS staff_name, d.name AS department_name
        FROM appointment a
        INNER JOIN patient p ON a.patient_id = p.patient_id
        LEFT JOIN staff s ON a.staff_id = s.staff_id
        LEFT JOIN department d ON s.dept_id = d.dept_id
        ORDER BY a.appointment_date DESC
        LIMIT 1000
    """)
    return jsonify(rows), 200

# Departments revenue (complex WITH + joins + group by)
@app.route('/api/departments/revenue', methods=['GET'])
def api_departments_revenue():
    rows = fetch_all("""
        WITH latest_record AS (
            SELECT DISTINCT ON (patient_id) patient_id, staff_id
            FROM medicalrecord
            ORDER BY patient_id, visit_date DESC
        )
        SELECT d.dept_id, d.name AS department_name, COALESCE(SUM(i.amount),0) AS total_amount
        FROM department d
        LEFT JOIN staff s ON s.dept_id = d.dept_id
        LEFT JOIN latest_record lr ON lr.staff_id = s.staff_id
        LEFT JOIN invoice i ON i.patient_id = lr.patient_id
        GROUP BY d.dept_id, d.name
        HAVING COALESCE(SUM(i.amount),0) >= 0
        ORDER BY total_amount DESC
    """)
    return jsonify(rows), 200

# Doctors busy (count appointments in a month)
@app.route('/api/doctors/busy', methods=['GET'])
def api_doctors_busy():
    month = request.args.get('month')
    threshold = int(request.args.get('threshold', 5))
    if not month:
        return jsonify({'error': 'missing month param e.g. 2025-10'}), 400
    try:
        start = parse_date(month + "-01").date()
    except Exception:
        return jsonify({'error': 'invalid month format'}), 400
    if start.month < 12:
        end = start.replace(month=start.month+1, day=1)
    else:
        end = start.replace(year=start.year+1, month=1, day=1)
    rows = fetch_all("""
        SELECT s.staff_id, s.name, COUNT(a.appointment_id) AS cnt
        FROM staff s
        LEFT JOIN appointment a ON a.staff_id=s.staff_id
          AND a.appointment_date >= %s AND a.appointment_date < %s
        GROUP BY s.staff_id, s.name
        HAVING COUNT(a.appointment_id) > %s
        ORDER BY cnt DESC
    """, (start, end, threshold))
    return jsonify(rows), 200

# Patients with unpaid invoices
@app.route('/api/patients/unpaid', methods=['GET'])
def api_patients_unpaid():
    rows = fetch_all("""
        SELECT DISTINCT p.*
        FROM patient p
        JOIN invoice i ON i.patient_id = p.patient_id
        WHERE i.status = 'Unpaid' OR i.status = 'Unpaid'
    """)
    return jsonify(rows), 200

# -------------------------
# Error handlers (JSON fallback)
# -------------------------
@app.errorhandler(404)
def api_page_not_found(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'not found'}), 404
    return jsonify({'error': 'not found'}), 404

@app.errorhandler(400)
def api_bad_request(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'bad request', 'message': str(e)}), 400
    return jsonify({'error': 'bad request'}), 400

@app.errorhandler(500)
def api_server_error(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'server error', 'message': str(e)}), 500
    return jsonify({'error': 'server error'}), 500